insert overwrite table res_oilb
SELECT SUBSTR(transaction_date,1,16),station_id,
sum(case WHEN trim(X.product_code)='92#' THEN 1 ELSE 0 END )AS mumber92,
SUM(CASE WHEN trim(X.product_code)='92#' THEN item_quantity ELSE 0 END) AS LITRE92,
SUM(CASE WHEN trim(X.product_code)='92#' THEN item_value ELSE 0 END)AS MONEY92,
sum(case WHEN trim(X.product_code)='95#' THEN 1 ELSE 0 END )AS mumber95,
SUM(CASE WHEN trim(X.product_code)='95#' THEN item_quantity ELSE 0 END) AS LITRE95,
SUM(CASE WHEN trim(X.product_code)='95#' THEN item_value ELSE 0 END)AS MONEY95,
sum(case WHEN trim(X.product_code)='97#' THEN 1 ELSE 0 END )AS mumber97,
SUM(CASE WHEN trim(X.product_code)='97#' THEN item_quantity ELSE 0 END) AS LITRE97,
SUM(CASE WHEN trim(X.product_code)='97#' THEN item_value ELSE 0 END)AS MONEY97,
sum(case WHEN trim(X.product_code)='0#' THEN 1 ELSE 0 END )AS mumber0,
SUM(CASE WHEN trim(X.product_code)='0#' THEN item_quantity ELSE 0 END) AS LITRE0,
SUM(CASE WHEN trim(X.product_code)='0#' THEN item_value ELSE 0 END)AS MONEY0,
sum(case WHEN trim(X.product_code)='-10#' THEN 1 ELSE 0 END )AS mumber_10,
SUM(CASE WHEN trim(X.product_code)='-10#' THEN item_quantity ELSE 0 END) AS LITRE_10,
SUM(CASE WHEN trim(X.product_code)='-10#' THEN item_value ELSE 0 END)AS MONEY_10,
sum(case WHEN trim(X.product_code)='-20#' THEN 1 ELSE 0 END )AS mumber_20,
SUM(CASE WHEN trim(X.product_code)='-20#' THEN item_quantity ELSE 0 END) AS LITRE_20,
SUM(CASE WHEN trim(X.product_code)='-20#' THEN item_value ELSE 0 END)AS MONEY_20,
sum(case WHEN trim(X.product_code)='98#' THEN 1 ELSE 0 END )AS mumber98,
SUM(CASE WHEN trim(X.product_code)='98#' THEN item_quantity ELSE 0 END) AS LITRE98,
SUM(CASE WHEN trim(X.product_code)='98#' THEN item_value ELSE 0 END)AS MONEY98,
sum(case WHEN trim(X.product_code)='80#' THEN 1 ELSE 0 END )AS mumber80,
SUM(CASE WHEN trim(X.product_code)='80#' THEN item_quantity ELSE 0 END) AS LITRE80,
SUM(CASE WHEN trim(X.product_code)='80#' THEN item_value ELSE 0 END)AS MONEY80,
sum(case WHEN trim(X.product_code)='-35#' THEN 1 ELSE 0 END )AS mumber_35,
SUM(CASE WHEN trim(X.product_code)='-35#' THEN item_quantity ELSE 0 END) AS LITRE_35,
SUM(CASE WHEN trim(X.product_code)='-35#' THEN item_value ELSE 0 END)AS MONEY_35
FROM
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value
from mid.transactions as Z join mid.transaction_items_oil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where Y.item_type!=20
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600)AS X
group by SUBSTR(transaction_date,1,16),station_id;

insert overwrite table res_oilb
select trim(minutes),stationid,
sum(number92),sum(litre92),sum(money92),sum(number95),
sum(litre95),sum(money95),sum(number97),sum(litre97),sum(money97),sum(number0),sum(litre0),
sum(money0),sum(numbe_10),sum(litre_10),sum(money_10),sum(number_20),sum(litre_20),
sum(money_20),sum(number98),sum(litre98),sum(money98),sum(number80),sum(litre80),sum(money80)
,sum(number_35),sum(litre_35),sum(money_35)
 from (
    SELECT SUBSTR(transaction_date,1,16) as minutes,stationid,
    sum(case WHEN trim(X.oils)='92#' THEN oilNumber ELSE 0 END )AS number92,
    SUM(CASE WHEN trim(X.oils)='92#' THEN item_quantity ELSE 0 END) AS LITRE92,
    SUM(CASE WHEN trim(X.oils)='92#' THEN item_value ELSE 0 END)AS MONEY92,
    sum(case WHEN trim(X.oils)='95#' THEN oilNumber ELSE 0 END )AS number95,
    SUM(CASE WHEN trim(X.oils)='95#' THEN item_quantity ELSE 0 END) AS LITRE95,
    SUM(CASE WHEN trim(X.oils)='95#' THEN item_value ELSE 0 END)AS MONEY95,
    sum(case WHEN trim(X.oils)='97#' THEN oilNumber ELSE 0 END )AS number97,
    SUM(CASE WHEN trim(X.oils)='97#' THEN item_quantity ELSE 0 END) AS LITRE97,
    SUM(CASE WHEN trim(X.oils)='97#' THEN item_value ELSE 0 END)AS MONEY97,
    sum(case WHEN trim(X.oils)='0#' THEN oilNumber ELSE 0 END )AS number0,
    SUM(CASE WHEN trim(X.oils)='0#' THEN item_quantity ELSE 0 END) AS LITRE0,
    SUM(CASE WHEN trim(X.oils)='0#' THEN item_value ELSE 0 END)AS MONEY0,
    sum(case WHEN trim(X.oils)='-10#' THEN oilNumber ELSE 0 END )AS numbe_10,
    SUM(CASE WHEN trim(X.oils)='-10#' THEN item_quantity ELSE 0 END) AS LITRE_10,
    SUM(CASE WHEN trim(X.oils)='-10#' THEN item_value ELSE 0 END)AS MONEY_10,
    sum(case WHEN trim(X.oils)='-20#' THEN oilNumber ELSE 0 END )AS number_20,
    SUM(CASE WHEN trim(X.oils)='-20#' THEN item_quantity ELSE 0 END) AS LITRE_20,
    SUM(CASE WHEN trim(X.oils)='-20#' THEN item_value ELSE 0 END)AS MONEY_20,
    sum(case WHEN trim(X.oils)='98#' THEN oilNumber ELSE 0 END )AS number98,
    SUM(CASE WHEN trim(X.oils)='98#' THEN item_quantity ELSE 0 END) AS LITRE98,
    SUM(CASE WHEN trim(X.oils)='98#' THEN item_value ELSE 0 END)AS MONEY98,
    sum(case WHEN trim(X.oils)='80#' THEN oilNumber ELSE 0 END )AS number80,
    SUM(CASE WHEN trim(X.oils)='80#' THEN item_quantity ELSE 0 END) AS LITRE80,
    SUM(CASE WHEN trim(X.oils)='80#' THEN item_value ELSE 0 END)AS MONEY80,
    sum(case WHEN trim(X.oils)='-35#' THEN oilNumber ELSE 0 END )AS number_35,
    SUM(CASE WHEN trim(X.oils)='-35#' THEN item_quantity ELSE 0 END) AS litre_35,
    SUM(CASE WHEN trim(X.oils)='-35#' THEN item_value ELSE 0 END)AS money_35
    FROM
    (SELECT minutes as transaction_date,stationID ,oils,oilNumber,oilLitre as item_quantity,oilMoney as item_value from res_oil_history) as X
    group by SUBSTR(transaction_date,1,16),stationID
    union all
    select minutes,stationid,number92,litre92,money92,
    number95,litre95,money95,number97,litre97,money97,number0,
    litre0,money0,numbe_10,litre_10,money_10,number_20,litre_20,
    money_20,number98,litre98,money98,number80,litre80,money80,number_35,litre_35,money_35 from res_oilb
) as c
group by trim(minutes),stationid;

insert overwrite table res_oilb_vip
SELECT SUBSTR(SENDTIME,1,16),trim(STATIONCODE),
sum(case WHEN trim(OILCODE) ='92#' THEN 1 ELSE 0 END )AS mumber92,
SUM(CASE WHEN trim(OILCODE) ='92#' THEN LITRE/1000 ELSE 0 END) AS LITRE92,
SUM(CASE WHEN trim(OILCODE) ='92#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY92,
sum(case WHEN trim(OILCODE) ='95#' THEN 1 ELSE 0 END )AS mumber95,
SUM(CASE WHEN trim(OILCODE) ='95#' THEN LITRE/1000 ELSE 0 END) AS LITRE95,
SUM(CASE WHEN trim(OILCODE) ='95#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY95,
sum(case WHEN trim(OILCODE) ='97#' THEN 1 ELSE 0 END )AS mumber97,
SUM(CASE WHEN trim(OILCODE) ='97#' THEN LITRE/1000 ELSE 0 END) AS LITRE97,
SUM(CASE WHEN trim(OILCODE) ='97#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY97,
sum(case WHEN trim(OILCODE) ='0#' THEN 1 ELSE 0 END )AS mumber0,
SUM(CASE WHEN trim(OILCODE) ='0#' THEN LITRE/1000 ELSE 0 END) AS LITRE0,
SUM(CASE WHEN trim(OILCODE) ='0#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY0,
sum(case WHEN trim(OILCODE) ='-10#' THEN 1 ELSE 0 END )AS mumber_10,
SUM(CASE WHEN trim(OILCODE) ='-10#' THEN LITRE/1000 ELSE 0 END) AS LITRE_10,
SUM(CASE WHEN trim(OILCODE) ='-10#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY_10,
sum(case WHEN trim(OILCODE) ='-20#' THEN 1 ELSE 0 END )AS mumber_20,
SUM(CASE WHEN trim(OILCODE) ='-20#' THEN LITRE/1000 ELSE 0 END) AS LITRE_20,
SUM(CASE WHEN trim(OILCODE) ='-20#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY_20,
sum(case WHEN trim(OILCODE)='98#' THEN 1 ELSE 0 END )AS mumber98,
SUM(CASE WHEN trim(OILCODE)='98#' THEN LITRE/1000 ELSE 0 END) AS LITRE98,
SUM(CASE WHEN trim(OILCODE)='98#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY98,
sum(case WHEN trim(OILCODE)='80#' THEN 1 ELSE 0 END )AS mumber80,
SUM(CASE WHEN trim(OILCODE)='80#' THEN LITRE/1000 ELSE 0 END) AS LITRE80,
SUM(CASE WHEN trim(OILCODE)='80#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY80,
sum(case WHEN trim(OILCODE)='-35#' THEN 1 ELSE 0 END )AS mumber_35,
SUM(CASE WHEN trim(OILCODE)='-35#' THEN LITRE/1000 ELSE 0 END) AS LITRE_35,
SUM(CASE WHEN trim(OILCODE)='-35#' THEN REALAMOUNT/100 ELSE 0 END)AS MONEY_35
FROM TBL_ORDERDETAILS_ALL WHERE OILCODE IS NOT NULL and ORDER_DEAL = 4
GROUP BY SUBSTR(SENDTIME,1,16),trim(STATIONCODE);

insert OVERWRITE table res_mopb
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(X.mop_name) = '斑马支付' then 1 ELSE 0 end) as zebpayNumber,
sum(case when trim(X.mop_name) = '斑马支付' then item_value ELSE 0 end)as zebpayMoney,
sum(case when trim(X.mop_name) = '微车支付' then 1 ELSE 0 end) as microcarNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as microcarMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from mid.transactions as Z 
join mid.transaction_items_mop as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where Y.item_value > 0 and Y.item_value < 100000 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;


insert OVERWRITE table res_mopb_vip
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(X.mop_name) = '斑马支付' then 1 ELSE 0 end) as zebpayNumber,
sum(case when trim(X.mop_name) = '斑马支付' then item_value ELSE 0 end)as zebpayMoney,
sum(case when trim(X.mop_name) = '微车支付' then 1 ELSE 0 end) as microcarNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as microcarMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from transactions_vip as Z 
join transaction_items_mop_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where Y.item_value > 0 and Y.item_value < 100000 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;

set hive.auto.convert.join=false;

insert OVERWRITE table res_mophhtb
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select B.transaction_date,A.station_id,A.mop_name,A.item_quantity,A.item_value
    from mid.transaction_items_mop as A
    join
    (select Z.station_id,Z.transaction_id,Z.transaction_date
        from
        mid.transactions AS Z
        JOIN mid.cashiers AS Y
        on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
     ) as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where A.item_value > 0 and A.item_value < 100000 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;



insert OVERWRITE table res_mophhtb_vip
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select B.transaction_date,A.station_id,A.mop_name,A.item_quantity,A.item_value
from transaction_items_mop_vip as A
join
    (select Z.station_id,Z.transaction_id,Z.transaction_date
    from
    transactions_vip AS Z
    JOIN mid.cashiers AS Y
    on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
    ) as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where A.item_value > 0 and A.item_value < 100000 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;

set hive.auto.convert.join=false;

insert OVERWRITE table res_mopiptb
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(x.mop_name) = '斑马支付' then 1 ELSE 0 end) as zebpayNumber,
sum(case when trim(x.mop_name) = '斑马支付' then item_value ELSE 0 end)as zebpayMoney,
sum(case when trim(X.mop_name) = '微车支付' then 1 ELSE 0 end) as microcarNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as microcarMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select B.transaction_date,A.station_id,A.mop_name,A.item_quantity,A.item_value
from mid.transaction_items_mop as A
join
    (select Z.station_id,Z.transaction_id,Z.transaction_date
    from
    mid.transactions AS Z
    JOIN mid.cashiers AS Y
    on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where A.item_value > 0 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;

insert OVERWRITE table res_mopiptb_vip
select substr(X.transaction_date,1,10),station_id,
sum(case when trim(X.mop_name) = 'EPS会员' THEN 1 ELSE 0 END)AS EPSNumber,
sum(case when trim(X.mop_name) = 'EPS会员' then item_value ELSE 0 end)as EPSMoney,
sum(case when trim(X.mop_name) = '优惠券' THEN 1 ELSE 0 END)AS couponNumber,
sum(case when trim(X.mop_name) = '优惠券' then item_value ELSE 0 end)as couponMoney,
sum(case when trim(X.mop_name) = '会员优惠券' THEN 1 ELSE 0 END)AS vipCouponNumber,
sum(case when trim(X.mop_name) = '会员优惠券' then item_value ELSE 0 end)as vipCouponNumber,
sum(case when trim(X.mop_name) = '信用卡' THEN 1 ELSE 0 END)AS creditCardNumber,
sum(case when trim(X.mop_name) = '信用卡' then item_value ELSE 0 end)as creditCardMoney,
sum(case when trim(X.mop_name) = '壳牌车队卡' THEN 1 ELSE 0 END)AS teamCardNumber,
sum(case when trim(X.mop_name) = '壳牌车队卡' then item_value ELSE 0 end)as teamCardMoney,
sum(case when trim(X.mop_name) = '微信支付' THEN 1 ELSE 0 END)AS wechatNumber,
sum(case when trim(X.mop_name) = '微信支付' then item_value ELSE 0 end)as wechatMoney,
sum(case when trim(X.mop_name) = '微车支付' THEN 1 ELSE 0 END)AS weichepayNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as weichepayMoney,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' THEN 1 ELSE 0 END)AS alipayNumber,
sum(case when trim(X.mop_name) = '支付宝' OR X.mop_name = '支付宝支付' then item_value ELSE 0 end)as alipayMoney,
sum(case when trim(X.mop_name) = '支票' THEN 1 ELSE 0 END)AS chequeNumber,
sum(case when trim(X.mop_name) = '支票' then item_value ELSE 0 end)as chequeMoney,
sum(case when trim(X.mop_name) = '滴滴支付' THEN 1 ELSE 0 END)AS didiNumber,
sum(case when trim(X.mop_name) = '滴滴支付' then item_value ELSE 0 end)as didiMoney,
sum(case when trim(X.mop_name) = '现金' THEN 1 ELSE 0 END)AS cashNumber,
sum(case when trim(X.mop_name) = '现金' then item_value ELSE 0 end)as cashMoney,
sum(case when trim(X.mop_name) = '电子支付优惠' THEN 1 ELSE 0 END)AS ePaymentNumber,
sum(case when trim(X.mop_name) = '电子支付优惠' then item_value ELSE 0 end)as ePaymentMoney,
sum(case when trim(X.mop_name) = '百度支付' THEN 1 ELSE 0 END)AS baiduNumber,
sum(case when trim(X.mop_name) = '百度支付' then item_value ELSE 0 end)as baiduNumber,
sum(case when trim(X.mop_name) = '第三方卡' THEN 1 ELSE 0 END)AS thirdPaymentNumber,
sum(case when trim(X.mop_name) = '第三方卡' then item_value ELSE 0 end)as thirdPaymentMoney,
sum(case when trim(X.mop_name) = '车到收款' THEN 1 ELSE 0 END)AS carInNumber,
sum(case when trim(X.mop_name) = '车到收款' then item_value ELSE 0 end)as carInMoney,
sum(case when trim(X.mop_name) = '银联钱包优惠券' THEN 1 ELSE 0 END)AS unionpayCouponNumber,
sum(case when trim(X.mop_name) = '银联钱包优惠券' then item_value ELSE 0 end)as unionpayCouponMoney,
sum(case when trim(x.mop_name) = '斑马支付' then 1 ELSE 0 end) as zebpayNumber,
sum(case when trim(x.mop_name) = '斑马支付' then item_value ELSE 0 end)as zebpayMoney,
sum(case when trim(X.mop_name) = '微车支付' then 1 ELSE 0 end) as microcarNumber,
sum(case when trim(X.mop_name) = '微车支付' then item_value ELSE 0 end)as microcarMoney,
sum(case when trim(X.mop_name) = '银行异业优惠' then 1 ELSE 0 end) as bankdiscountNumber,
sum(case when trim(X.mop_name) = '银行异业优惠' then item_value ELSE 0 end)as bankdiscountMoney
from
(select B.transaction_date,A.station_id,A.mop_name,A.item_quantity,A.item_value
from transaction_items_mop_vip as A
join
      (select Z.station_id,Z.transaction_id,Z.transaction_date
      from
      transactions_vip AS Z
      JOIN mid.cashiers AS Y
      on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT'
      ) as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where A.item_value > 0 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id;

set hive.auto.convert.join=true;

insert overwrite table res_notoilDepartmentb_vip
select substr(X.transaction_date,1,16),X.station_id,
sum(case WHEN trim(X.dept_name)='店内服务' THEN 1 ELSE 0 END )AS instoreNumber,
SUM(CASE WHEN trim(X.dept_name)='店内服务' THEN X.item_value ELSE 0 END) AS instoreMoney,
sum(case WHEN trim(X.dept_name)='快餐产品' THEN 1 ELSE 0 END )AS fastfoodNumber,
SUM(CASE WHEN trim(X.dept_name)='快餐产品' THEN X.item_value ELSE 0 END) AS fastfoodMoney,
sum(case WHEN trim(X.dept_name)='易腐产品' THEN 1 ELSE 0 END )AS perishableNumber,
SUM(CASE WHEN trim(X.dept_name)='易腐产品' THEN X.item_value ELSE 0 END) AS perishableMoney,
sum(case WHEN trim(X.dept_name)='润滑油' THEN 1 ELSE 0 END )AS lubeNumber,
SUM(CASE WHEN trim(X.dept_name)='润滑油' THEN X.item_value ELSE 0 END) AS lubeMoney,
sum(case WHEN trim(X.dept_name)='烟草' THEN 1 ELSE 0 END )AS cigaretteNumber,
SUM(CASE WHEN trim(X.dept_name)='烟草' THEN X.item_value ELSE 0 END) AS cigaretteMoney,
sum(case WHEN trim(X.dept_name)='生活日用品' THEN 1 ELSE 0 END )AS dailyNumber,
SUM(CASE WHEN trim(X.dept_name)='生活日用品' THEN X.item_value ELSE 0 END) AS dailyMoney,
sum(case WHEN trim(X.dept_name)='车队卡(卡费)' THEN 1 ELSE 0 END )AS teamcardNumber,
SUM(CASE WHEN trim(X.dept_name)='车队卡(卡费)' THEN X.item_value ELSE 0 END) AS teamcardMoney,
sum(case WHEN trim(X.dept_name)='酒精饮料' THEN 1 ELSE 0 END )AS alcoholicNumber,
SUM(CASE WHEN trim(X.dept_name)='酒精饮料' THEN X.item_value ELSE 0 END) AS alcoholicMoney,
sum(case WHEN trim(X.dept_name)='零食' THEN 1 ELSE 0 END )AS snackNumber,
SUM(CASE WHEN trim(X.dept_name)='零食' THEN X.item_value ELSE 0 END) AS snackMoney,
sum(case WHEN trim(X.dept_name)='非酒精饮料' THEN 1 ELSE 0 END )AS nonalcoholicNumber,
SUM(CASE WHEN trim(X.dept_name)='非酒精饮料' THEN X.item_value ELSE 0 END) AS nonalcoholicNumber,
sum(case WHEN trim(X.dept_name)='非食品' THEN 1 ELSE 0 END )AS nonfoodNumber,
SUM(CASE WHEN trim(X.dept_name)='非食品' THEN X.item_value ELSE 0 END) AS nonfoodMoney
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name
from transactions_vip as Z 
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join mid.products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join mid.departments as C
on C.station_id = 50006 and B.department_id = C.department_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
)AS X
group by
substr(X.transaction_date,1,16),X.station_id;

insert overwrite table res_notoilDepartmentb
select substr(X.transaction_date,1,16),X.station_id,
sum(case WHEN trim(X.dept_name)='店内服务' THEN 1 ELSE 0 END )AS instoreNumber,
SUM(CASE WHEN trim(X.dept_name)='店内服务' THEN X.item_value ELSE 0 END) AS instoreMoney,
sum(case WHEN trim(X.dept_name)='快餐产品' THEN 1 ELSE 0 END )AS fastfoodNumber,
SUM(CASE WHEN trim(X.dept_name)='快餐产品' THEN X.item_value ELSE 0 END) AS fastfoodMoney,
sum(case WHEN trim(X.dept_name)='易腐产品' THEN 1 ELSE 0 END )AS perishableNumber,
SUM(CASE WHEN trim(X.dept_name)='易腐产品' THEN X.item_value ELSE 0 END) AS perishableMoney,
sum(case WHEN trim(X.dept_name)='润滑油' THEN 1 ELSE 0 END )AS lubeNumber,
SUM(CASE WHEN trim(X.dept_name)='润滑油' THEN X.item_value ELSE 0 END) AS lubeMoney,
sum(case WHEN trim(X.dept_name)='烟草' THEN 1 ELSE 0 END )AS cigaretteNumber,
SUM(CASE WHEN trim(X.dept_name)='烟草' THEN X.item_value ELSE 0 END) AS cigaretteMoney,
sum(case WHEN trim(X.dept_name)='生活日用品' THEN 1 ELSE 0 END )AS dailyNumber,
SUM(CASE WHEN trim(X.dept_name)='生活日用品' THEN X.item_value ELSE 0 END) AS dailyMoney,
sum(case WHEN trim(X.dept_name)='车队卡(卡费)' THEN 1 ELSE 0 END )AS teamcardNumber,
SUM(CASE WHEN trim(X.dept_name)='车队卡(卡费)' THEN X.item_value ELSE 0 END) AS teamcardMoney,
sum(case WHEN trim(X.dept_name)='酒精饮料' THEN 1 ELSE 0 END )AS alcoholicNumber,
SUM(CASE WHEN trim(X.dept_name)='酒精饮料' THEN X.item_value ELSE 0 END) AS alcoholicMoney,
sum(case WHEN trim(X.dept_name)='零食' THEN 1 ELSE 0 END )AS snackNumber,
SUM(CASE WHEN trim(X.dept_name)='零食' THEN X.item_value ELSE 0 END) AS snackMoney,
sum(case WHEN trim(X.dept_name)='非酒精饮料' THEN 1 ELSE 0 END )AS nonalcoholicNumber,
SUM(CASE WHEN trim(X.dept_name)='非酒精饮料' THEN X.item_value ELSE 0 END) AS nonalcoholicNumber,
sum(case WHEN trim(X.dept_name)='非食品' THEN 1 ELSE 0 END )AS nonfoodNumber,
SUM(CASE WHEN trim(X.dept_name)='非食品' THEN X.item_value ELSE 0 END) AS nonfoodMoney
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name
from mid.transactions as Z 
join mid.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join mid.products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join mid.departments as C
on C.station_id = 50006 and B.department_id = C.department_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
)AS X
group by
substr(X.transaction_date,1,16),X.station_id;

